*Clean DTCA data

import excel "Rx_Ads (1998-2009).xlsx", sheet("Rx_Ads") firstrow

drop ULTIMATEOWNER PARENT SUBSIDIARY ADVERTISER BRAND

replace PRODUCT = upper(PRODUCT)

*Drop everything after the colon in the product variable
replace PRODUCT = substr(PRODUCT, 1, strpos(PRODUCT, " :") - 1)  if strpos(PRODUCT, " :") 
format PRODUCT %40s

rename ( NETWORKTVDOLS000 CABLETVDOLS000 SYNDICATIONDOLS000 SPOTTVDOLS000 MAGAZINESDOLS000 SUNDAYMAGSDOLS000 NATLNEWSPDOLS000 NEWSPAPERDOLS000 NETWORKRADIODOLS000 NATSPOTRADIODOLS000 USINTERNETDOLS000 OUTDOORDOLS000 ) (NETWORKTVDOLS CABLETVDOLS SYNDICATIONDOLS SPOTTVDOLS MAGAZINESDOLS SUNDAYMAGSDOLS NATLNEWSPDOLS NEWSPAPERDOLS NETWORKRADIODOLS NATSPOTRADIODOLS USINTERNETDOLS OUTDOORDOLS )

replace NETWORKTVDOLS = NETWORKTVDOLS * 1000
replace CABLETVDOLS = CABLETVDOLS * 1000
replace SYNDICATIONDOLS = SYNDICATIONDOLS * 1000
replace SPOTTVDOLS = SPOTTVDOLS * 1000
replace MAGAZINESDOLS = MAGAZINESDOLS * 1000
replace SUNDAYMAGSDOLS = SUNDAYMAGSDOLS * 1000
replace NATLNEWSPDOLS = NATLNEWSPDOLS * 1000
replace NEWSPAPERDOLS = NEWSPAPERDOLS * 1000
replace NETWORKRADIODOLS = NETWORKRADIODOLS * 1000
replace NATSPOTRADIODOLS = NATSPOTRADIODOLS * 1000
replace USINTERNETDOLS = USINTERNETDOLS * 1000
replace OUTDOORDOLS = OUTDOORDOLS * 1000

collapse (sum) NETWORKTVDOLS CABLETVDOLS SYNDICATIONDOLS SPOTTVDOLS MAGAZINESDOLS SUNDAYMAGSDOLS NATLNEWSPDOLS NEWSPAPERDOLS NETWORKRADIODOLS NATSPOTRADIODOLS USINTERNETDOLS OUTDOORDOLS, by(PRODUCT TIMEPERIOD)

save DTCA_1998_2009, replace



clear
use DTCA_1998_2009
 
keep if PRODUCT == "CRESTOR" | PRODUCT == "LIPITOR" | PRODUCT == "ZOCOR" | PRODUCT == "VYTORIN"

gen quarter = qofd(TIMEPERIOD)
format quarter %tq

gen year = year(TIMEPERIOD)

gen total_spend = NETWORKTVDOLS + CABLETVDOLS + SYNDICATIONDOLS + SPOTTVDOLS + MAGAZINESDOLS + SUNDAYMAGSDOLS + NATLNEWSPDOLS + NEWSPAPERDOLS + NETWORKRADIODOLS + NATSPOTRADIODOLS + USINTERNETDOLS + OUTDOORDOLS

collapse (sum) total_spend, by(PRODUCT year)


replace total_spend = total_spend / 1000000
line total_spend year if PRODUCT == "CRESTOR", ytitle("Total DTC Spending in U.S. Markets ($ million)") xtitle("Year") graphregion(color(white))
graph save crestor_dtc_by_year, replace
graph export crestor_dtc_by_year.pdf, replace
